package com.choudou5.solr.util.jdbc;

import cn.hutool.core.collection.CollUtil;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.map.MapUtil;
import com.alibaba.druid.util.JdbcUtils;
import com.choudou5.solr.util.BeanMapper;
import com.choudou5.solr.util.JsonUtil;
import com.choudou5.solr.util.StrUtil;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.sql.DataSource;
import java.io.Serializable;
import java.sql.*;
import java.util.*;
import java.util.Date;

/**
 * @Name：DBUtil 说明
 * @@author choudou5
 * @@date 2018-07-13
 */
public class DBUtil {

    private static final Logger logger = LoggerFactory.getLogger(DBUtil.class);

    private static DataSource dataSource = DataSourceFactory.getDataSource();

    /**
     * 插入数据
     * @param sql
     */
    public static void insertData(String sql) throws SQLException {
        logger.info("开始插入数据...");
        if (logger.isDebugEnabled()){
            logger.debug(sql);
        }
        JdbcUtils.executeUpdate(dataSource, sql);
    }

    /**
     * 插入数据
     * @param table
     * @param param
     */
    public static void insertData(String table, Map param) throws SQLException {
        if(MapUtil.isEmpty(param))
            return;
        logger.info("开始插入数据...");
        JdbcUtils.insertToTable(dataSource, table, param);
    }

    /**
     * 修改数据
     * @param sql
     * @param param
     */
    public static void updateData(String sql, Object param) throws SQLException {
        logger.info("开始修改数据...");
        if (logger.isDebugEnabled()){
            logger.debug(sql);
            logger.debug(JsonUtil.toStr(param));
        }
        JdbcUtils.executeUpdate(dataSource, sql, param);
    }

    /**
     * 修改数据
     * @param sql
     */
    public static int updateData(String sql) throws SQLException {
        logger.info("开始修改数据...");
        if (logger.isDebugEnabled()){
            logger.debug(sql);
        }
        return JdbcUtils.executeUpdate(dataSource, sql);
    }


    /**
     * 查询最大ID
     * @param table
     * @param defVal 默认值
     * @return
     * @throws SQLException
     */
    public static Integer getMaxId(String table, int defVal) {
        try {
            String sql = "SELECT MAX(ID) FROM "+table;
            Object val = findOneColumn(sql);
            if(val != null)
                return Integer.parseInt(val.toString())+1;
        } catch (SQLException e) {
            logger.error("getMaxId fail.", e);
        }
        return defVal;
    }

    public static Long findCountBySql(String sql) throws SQLException {
        if (logger.isDebugEnabled()){
            logger.debug(sql);
        }
        try {
            Object val = findOneColumn(sql);
            return Long.valueOf(val.toString());
        } catch (SQLException e) {
            logger.error("findCountBySql fail.", e);
            throw e;
        }
    }


    public static Long findCount(String table, String where) throws SQLException {
        String sql = "SELECT COUNT(1) FROM "+table +" WHERE "+ where;
        if (logger.isDebugEnabled()){
            logger.debug(sql);
        }
        try {
            Object val = findOneColumn(sql);
            return Long.valueOf(val.toString());
        } catch (SQLException e) {
            logger.error("findCount fail.", e);
            throw e;
        }
    }


    public static Long findAllCount(String table) throws SQLException {
        String sql = "SELECT COUNT(1) FROM "+table;
        if (logger.isDebugEnabled()){
            logger.debug(sql);
        }
        try {
            Object val = findOneColumn(sql);
            return Long.valueOf(val.toString());
        } catch (SQLException e) {
            logger.error("findAllCount fail.", e);
            throw e;
        }
    }

    public static Object findOneColumn(String sql) throws SQLException {
        if (logger.isDebugEnabled()){
            logger.debug(sql);
        }
        Connection conn = null;
        Object value = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = dataSource.getConnection();
            stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery();
            while (rs.next()) {
                if(value != null)
                    throw new SQLException("查询出多条");
                value = rs.getObject(1);
            }
        }  catch (SQLException e){
            logger.error("findOneColumn fail.", e);
            throw e;
        } finally {
            close(rs, stmt, conn);
        }
        return value;
    }

    public static List<Long> findOneLongColumnList(String sql) throws SQLException {
        return findOneColumnList(sql, Long.class);
    }

    public static List<String> findOneStrColumnList(String sql) throws SQLException {
        return findOneColumnList(sql, String.class);
    }

    public static List<Integer> findOneIntColumnList(String sql) throws SQLException {
        return findOneColumnList(sql, Integer.class);
    }

    public static <T extends Serializable> List<T> findOneColumnList(String sql, Class<T> clasz) throws SQLException {
        if (logger.isDebugEnabled())
            logger.debug(sql);
        Connection conn = null;
        List result = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = dataSource.getConnection();
            stmt = conn.prepareStatement(sql);
            rs = stmt.executeQuery();
            result = new ArrayList();
            while (rs.next()) {
                if(clasz == String.class)
                    if(rs.getObject(1) instanceof byte[]){
                        result.add(StrUtil.asciiToStr((byte[])rs.getObject(1)));
                    }else{
                        result.add(rs.getString(1));
                    }
                else if(clasz == Long.class)
                    result.add(rs.getLong(1));
                else if(clasz == Integer.class)
                    result.add(rs.getInt(1));
                else
                    result.add(rs.getString(1));
            }
        }  catch (SQLException e){
            logger.error("findOneColumnList fail.", e);
            throw e;
        } finally {
            close(rs, stmt, conn);
        }
        return result;
    }


    /**
     * 查询单条
     * @param sql
     * @return
     */
    public static <T> T findOne(String sql, Class<T> clasz) throws SQLException {
        Map<String, Object> obj = findOneByParam(sql, null);
        return obj==null?null: BeanMapper.map(obj, clasz);
    }


    /**
     * 查询单条
     * @param sql
     * @param parameters
     * @return
     */
    public static Map<String, Object> findOneByParam(String sql, List<Object> parameters) throws SQLException {
        if (logger.isDebugEnabled()){
            logger.debug(sql);
            logger.debug(StrUtil.join(parameters, ","));
        }
        Connection conn = null;
        Map<String, Object> row = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = dataSource.getConnection();
            stmt = conn.prepareStatement(sql);
            if(CollUtil.isNotEmpty(parameters))
                setParameters(stmt, parameters);
            rs = stmt.executeQuery();
            ResultSetMetaData rsMeta = rs.getMetaData();
            while (rs.next()) {
                if(row != null)
                    throw new SQLException("查询出多条");
                row = new LinkedHashMap<>();
                for (int i = 0, size = rsMeta.getColumnCount(); i < size; ++i) {
                    String columName = rsMeta.getColumnLabel(i + 1);
                    Object value = rs.getObject(i + 1);
                    row.put(StrUtil.toJavaVariableName(columName), value);
                }
            }
        } catch (SQLException e){
            logger.error("findOneByParam fail.", e);
            throw e;
        } finally {
            close(rs, stmt, conn);
        }
        return row;
    }


    /**
     * 查询列表数据
     * @param sql
     * @return
     */
    public static <T> List<T> findList(String sql, Class<T> clasz) throws SQLException {
        List<Map<String, Object>> rows = findListByParam(sql, null);
        return rows.isEmpty()?null: BeanMapper.mapList(rows, clasz);
    }


    public static List<Map<String, Object>> findListByParam(String sql, List<Object> parameters) throws SQLException {
        if (logger.isDebugEnabled()){
            logger.debug(sql);
            logger.debug(StrUtil.join(parameters, ","));
        }
        List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            conn = dataSource.getConnection();
            stmt = conn.prepareStatement(sql);
            if(CollUtil.isNotEmpty(parameters))
                setParameters(stmt, parameters);
            rs = stmt.executeQuery();
            ResultSetMetaData rsMeta = rs.getMetaData();
            while (rs.next()) {
                Map<String, Object> row = new LinkedHashMap<String, Object>();
                for (int i = 0, size = rsMeta.getColumnCount(); i < size; ++i) {
                    String columName = rsMeta.getColumnLabel(i + 1);
                    Object value = rs.getObject(i + 1);
                    row.put(StrUtil.toJavaVariableName(columName), value);
                }
                rows.add(row);
            }
        }  catch (SQLException e){
            logger.error("findListByParam fail.", e);
            throw e;
        }finally {
            close(rs, stmt, conn);
        }
        return rows;
    }


    private static void setParameters(PreparedStatement stmt, List<Object> parameters) throws SQLException {
        for (int i = 0, size = parameters.size(); i < size; ++i) {
            Object param = parameters.get(i);
            if(param instanceof java.util.Date)
                param = DateUtil.formatDateTime((Date)param);
            stmt.setObject(i + 1, param);
        }
    }

    public static void close(ResultSet rs, Statement stmt, Connection conn) {
         {
            try {
                if (rs != null) rs.close();
                if (stmt != null) stmt.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                logger.error("close error", e);
            }
        }
    }

}
